"""
Case Type   : DDL-function
Case Name   : 创建包含处理数组元素的函数进行调用
Create At   : 2023/04/03
Owner       : @zou_jialiang050
Description :
    1.创建函数
    2.执行函数
Expect      :
    1.成功
    2.成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class CreateFunction(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'----{os.path.dirname(__file__)} start----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.primary_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.function_name = 'f_ddl_create_fun_0158'

    def test_create_function(self):
        step = '--step1:创建函数 expect:成功--'
        self.logger.info(step)
        sql_cmd = f"""drop function if exists {self.function_name};
        create or replace function {self.function_name}(
        varchar,varchar, varchar)
        returns varchar
        as \$\$
        declare
        tokens varchar[];
        length integer;
        indexnum integer;
        begin
            tokens := pg_catalog.string_to_array(\$1, \$2);
            length := pg_catalog.array_upper(tokens, 1);
            indexnum := length - (\$3*-1) + 1;
            if \$3 >= 0 then
            return pg_catalog.array_to_string(tokens[1:\$3], \$2);
            else
            return pg_catalog.array_to_string(tokens[indexnum:length], \$2);
            end if;
        end;
        \$\$ immutable strict language plpgsql;"""
        self.logger.info(sql_cmd)
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(result)
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                      result, '执行失败' + step)

        step = '--step2:执行函数 expect:成功--'
        self.logger.info(step)
        result = self.primary_sh.execut_db_sql(
            f"select {self.function_name}('12,3,5,6',',',3);")
        self.logger.info(result)
        self.assertIn('1 row', result, '执行失败' + step)

    def tearDown(self):
        step = '--step2:清理环境 expect:成功--'
        self.logger.info(step)
        result = self.primary_sh.execut_db_sql(f'drop function '
                                               f'{self.function_name};')
        self.logger.info(result)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG,
                      result, '执行失败' + step)
        self.logger.info(f'----{os.path.dirname(__file__)} end----')

